#!/bin/bash
# Absolute path to this script, e.g. /home/user/bin/foo.sh
SCRIPT=$(readlink -f "$0")
# Absolute path this script is in, thus /home/user/bin
SCRIPTPATH=$(dirname "$SCRIPT")
# Max execution time of sql script
MAX_EXECUTION_TIME=300

# MySQL connection information
MYSQL_HOST='127.0.0.1'
MYSQL_PORT=3306
MYSQL_USER='monitor'
MYSQL_PASS='changeme'
MYSQL_COMM="mysql -N -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT} --database=information_schema -e "

# Mail settings
MAIL_TO='dba@domain.com;'
MAIL_SUBJECT='警告：执行时间超长SQL强行终止'
MAIL_MESSAGE='以下查询执行超时，已经被终止'

H=$(date +"%k") # 当前的小时数，24小时制

LINE=$(printf %67s | tr ' ' ':')

function urlencode()
{
	local LANG=C
	for ((i=0;i<${#1};i++)); do
		if [[ ${1:$i:1} =~ ^[a-zA-Z0-9\.\~\_\-]$ ]]; then
			printf "${1:$i:1}"
		else
			printf '%%%02X' "'${1:$i:1}"
		fi
	done
}

LOG="${SCRIPTPATH}/mst-kill-$(date '+%Y-%m-%d').log"
echo "`date '+%Y-%m-%d %H:%M:%S'` [INFO] ----------------------------------------" >> ${LOG}
echo "`date '+%Y-%m-%d %H:%M:%S'` [INFO] 进程唤醒" >> ${LOG}

CONTENT=""
SQL=""

SQL="${SQL}SELECT ID,COMMAND,INFO,TIME,USER,SUBSTRING_INDEX(HOST, ':', 1) HOST,MEMORY_USED,EXAMINED_ROWS "
SQL="${SQL}FROM information_schema.PROCESSLIST "
SQL="${SQL}WHERE ID <> CONNECTION_ID() "
SQL="${SQL}AND COMMAND = 'Query' "
SQL="${SQL}AND USER NOT IN ('root', 'system user', 'replicate', 'event_scheduler', 'review', 'backup') "
SQL="${SQL}AND TIME >= ${MAX_EXECUTION_TIME}"

# TODO:
while read row
do
	[ -z "$row" ] && break # 没有查询到信息则退出
	IFS="$(echo -e '\t')" read ID COMMAND INFO TIME USER HOST MEMORY_USED EXAMINED_ROWS <<< "${row}"
	CONTENT="${CONTENT}连接标识: ${ID}\n"
	CONTENT="${CONTENT}执行时间: ${TIME}\n"
	CONTENT="${CONTENT}执行语句: ${INFO}\n"
	CONTENT="${CONTENT}执行用户: ${USER}\n"
	CONTENT="${CONTENT}执行主机: ${HOST}\n"
	CONTENT="${CONTENT}内存使用：${MEMORY_USED}\n"
	CONTENT="${CONTENT}扫描行数：${EXAMINED_ROWS}\n"
	# 允许夜间仅警告
	if [[ ${H} -ge 9 && ${H} -le 20 ]]
	then
		CONTENT="${CONTENT}处理方式: kill\n"
		mysql -N -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT} --database=information_schema -e "kill ${ID}"
	else
		CONTENT="${CONTENT}处理方式: warn\n"
	fi
	CONTENT="${CONTENT}${LINE}\n"
done << EOF
$(mysql -N -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT} --database=information_schema -e "${SQL}")
EOF

# Remove last line from message block
echo -ne ${CONTENT} | sed '$d' >> ${LOG}
echo "`date '+%Y-%m-%d %H:%M:%S'` [INFO] 检查结束" >> ${LOG}

if [ -z "${CONTENT}" ] # 如果没有信息则退出
then
	exit 0
fi

MESSAGE=$(echo -ne ${CONTENT} | sed '$d')
MESSAGE=$(printf '%s\n\n%s' "${MAIL_MESSAGE}" "${MESSAGE}")
MESSAGE=$(urlencode "${MESSAGE}")

SUBJECT=$(urlencode "${MAIL_SUBJECT}")

DATA="tos=${MAIL_TO}&subject=${SUBJECT}&content=${MESSAGE}"

# TODO: 发送邮件
curl http://1.2.3.4/sender/mail -d "${DATA}" > /dev/null 2>&1 &

exit 0
